CREATE VIEW [dbo].[vSoaGroupMemberDetail]
AS
    SELECT  [GroupMemberDetailId],
            [GroupMemberId],
            [PartyId],
            [EffectiveDate],
            [ExpirationDate],
            CAST(CASE WHEN ( [ExpirationDate] IS NULL
                             OR [ExpirationDate] > DATEADD(DAY, 1, GETDATE())
                           )
                           AND ( [EffectiveDate] IS NULL
                                 OR [EffectiveDate] < GETDATE()
                               ) THEN 1
                      ELSE 0
                 END AS BIT) AS [IsActive],
            [GroupRoleId],
            [RoleName],
            [RoleDescription],
            [RolePriority],
            [RoleReciprocalName],
            [RoleReciprocalDescription],
            [Title],
            [Note]
      FROM  [dbo].[vSoaGroupMemberDetailSubscription]
    UNION ALL
    SELECT  [GroupMemberDetailId],
            [GroupMemberId],
            [PartyId],
            [EffectiveDate],
            [ExpirationDate],
            CAST(CASE WHEN ( [ExpirationDate] IS NULL
                             OR [ExpirationDate] > DATEADD(DAY, 1, GETDATE())
                           )
                           AND ( [EffectiveDate] IS NULL
                                 OR [EffectiveDate] < GETDATE()
                               ) THEN 1
                      ELSE 0
                 END AS BIT) AS [IsActive],
            CASE WHEN [cp].[POSITION_CODE] IS NOT NULL
                 THEN CAST('COMMITTEE:' + [cp].[POSITION_CODE] AS VARCHAR(30))
                 ELSE CAST(NULL AS VARCHAR(30))
            END AS [GroupRoleId],
            CAST([cp].[TITLE] AS VARCHAR(100)) AS [RoleName],
            CAST(NULL AS VARCHAR(100)) AS [RoleDescription],
            [cp].[RANK] AS [RolePriority],
            CAST(NULL AS VARCHAR(100)) AS [RoleReciprocalName],
            CAST(NULL AS VARCHAR(100)) AS [RoleReciprocalDescription],
            CAST(NULL AS VARCHAR(100)) AS [Title],
            CAST(NULL AS VARCHAR(6000)) AS [Note]
      FROM  [dbo].[vSoaGroupMemberDetailCommittee] AS [gmdc]
            LEFT OUTER JOIN [dbo].[Committee_Position] AS [cp] ON [gmdc].[ACTION_CODES] = [cp].[POSITION_CODE]
                                                              AND [gmdc].[ACTION_CODES] != ''
    UNION ALL
    SELECT  [gmdr].[GroupMemberDetailId],
            [gmdr].[GroupMemberId],
            [gmdr].[PartyId],
            [gmdr].[EffectiveDate],
            [gmdr].[ExpirationDate],
            CAST(CASE WHEN ( [gmdr].[ExpirationDate] IS NULL
                             OR [gmdr].[ExpirationDate] > DATEADD(DAY, 1,
                                                              GETDATE())
                           )
                           AND ( [gmdr].[EffectiveDate] IS NULL
                                 OR [gmdr].[EffectiveDate] < GETDATE()
                               )
                           AND ( [gmdr].[STATUS] IS NULL
                                 OR RTRIM([gmdr].[STATUS]) = ''
                                 OR [gmdr].[STATUS] = 'A'
                               ) THEN 1
                      ELSE 0
                 END AS BIT) AS [IsActive],
            [gmdr].[GroupRoleId],
            [gmdr].[RoleName],
            [gmdr].[RoleDescription],
            [gmdr].[RolePriority],
            CAST([rrt].[RELATION_TYPE] AS VARCHAR(100)) AS [RoleReciprocalName],
            CAST([rrt].[DESCRIPTION] AS VARCHAR(100)) AS [RoleReciprocalDescription],
            [gmdr].[Title],
            CAST([r].[NOTE] AS VARCHAR(6000)) AS [Note]
      FROM  [dbo].[vSoaGroupMemberDetailRelationship] AS [gmdr]
            INNER JOIN [dbo].[Relationship] r ON [r].SEQN = [gmdr].[SEQN]
            LEFT OUTER JOIN [dbo].[Relationship_Types] [rrt] ON [gmdr].[RoleName] = [rrt].[RELATION_TYPE]
    UNION ALL
    SELECT  [GroupMemberDetailId],
            [GroupMemberId],
            [PartyId],
            NULL AS [EffectiveDate],
            NULL AS [ExpirationDate],
            CAST(1 AS BIT) AS [IsActive],
            [GroupRoleId],
            CAST(NULL AS VARCHAR(100)) AS [RoleName],
            CAST(NULL AS VARCHAR(100)) AS [RoleDescription],
            CAST(NULL AS INT) AS [RolePriority],
            CAST(NULL AS VARCHAR(100)) AS [RoleReciprocalName],
            CAST(NULL AS VARCHAR(100)) AS [RoleReciprocalDescription],
            [Title],
            CAST(NULL AS VARCHAR(6000)) AS [Note]
      FROM  [dbo].[vSoaGroupMemberDetailOrganization]

GO